CREATE DATABASE  IF NOT EXISTS `camschooldb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `camschooldb`;
-- MySQL dump 10.13  Distrib 5.6.17, for Win64 (x86_64)
--
-- Host: localhost    Database: camschooldb
-- ------------------------------------------------------
-- Server version	5.6.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `absentlist`
--

DROP TABLE IF EXISTS `absentlist`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `absentlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `employeeId` int(11) DEFAULT NULL,
  `studentId` int(11) DEFAULT NULL,
  `reason` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `absentlist_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employee` (`id`),
  CONSTRAINT `absentlist_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `absentlist`
--

LOCK TABLES `absentlist` WRITE;
/*!40000 ALTER TABLE `absentlist` DISABLE KEYS */;
/*!40000 ALTER TABLE `absentlist` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `application`
--

DROP TABLE IF EXISTS `application`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `application` (
  `id` int(11) NOT NULL,
  `description` varchar(100) NOT NULL,
  `applicant` varchar(45) NOT NULL,
  `exectutedBy` varchar(45) NOT NULL,
  `status` varchar(45) NOT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `application`
--

LOCK TABLES `application` WRITE;
/*!40000 ALTER TABLE `application` DISABLE KEYS */;
/*!40000 ALTER TABLE `application` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `classroom`
--

DROP TABLE IF EXISTS `classroom`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classroom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `infrastructureCategoryId` int(11) NOT NULL,
  `numberOfSits` int(11) NOT NULL,
  `numberOfStudents` int(11) NOT NULL,
  `area` varchar(255) NOT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `state` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniClassroom` (`id`,`name`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=50025 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classroom`
--

LOCK TABLES `classroom` WRITE;
/*!40000 ALTER TABLE `classroom` DISABLE KEYS */;
INSERT INTO `classroom` VALUES (50001,'6 eme A',70001,40,39,'90 m2','\0','2001-08-11','2001-08-11','new'),(50002,'6 eme B',70001,39,39,'90 m2','\0','2001-08-11','2001-08-11','new'),(50003,'6 eme C',70001,39,39,'91 m2','\0','2001-08-11','2001-08-11','new'),(50004,'6 eme D',70001,41,39,'92 m2','\0','2001-08-11','2001-08-11','new'),(50005,'6 eme E',70001,41,39,'92 m2','\0','2001-08-11','2001-08-11','new'),(50006,'6 eme F',70001,41,39,'92 m2','\0','2001-08-11','2001-08-11','new'),(50007,'5 eme A',70002,41,40,'92 m2','\0','2001-08-11','2001-08-11','new'),(50008,'5 eme B',70002,41,40,'92 m2','\0','2001-08-11','2001-08-11','new'),(50009,'5 eme C',70002,41,40,'92 m2','\0','2001-08-11','2001-08-11','new'),(50010,'5 eme D',70002,41,40,'92 m2','\0','2001-08-11','2001-08-11','new'),(50011,'4 eme A',70003,37,37,'82 m2','\0','2001-08-11','2001-08-11','new'),(50012,'4 eme B',70003,37,37,'82 m2','\0','2001-08-11','2001-08-11','new'),(50013,'4 eme C',70003,37,37,'82 m2','\0','2001-08-11','2001-08-11','new'),(50014,'3 eme A',70004,30,30,'80 m2','\0','2001-08-11','2001-08-11','new'),(50015,'3 eme B',70004,31,30,'80 m2','\0','2001-08-11','2001-08-11','new'),(50016,'3 eme C',70004,32,30,'80 m2','\0','2001-08-11','2001-08-11','new'),(50017,'2nde A',70005,30,30,'80 m2','\0','2001-08-11','2001-08-11','new'),(50018,'2nde C',70005,35,35,'80 m2','\0','2001-08-11','2001-08-11','new'),(50019,'1ere A',70006,30,30,'80 m2','\0','2001-08-11','2001-08-11','new'),(50020,'1ere C',70006,25,25,'80 m2','\0','2001-08-11','2001-08-11','new'),(50021,'1ere D',70006,35,35,'80 m2','\0','2001-08-11','2001-08-11','new'),(50022,'Tle A',70007,35,35,'80 m2','\0','2001-08-11','2001-08-11','new'),(50023,'Tle C',70007,25,25,'80 m2','\0','2001-08-11','2001-08-11','new'),(50024,'Tle D',70007,35,35,'80 m2','\0','2001-08-11','2001-08-11','new');
/*!40000 ALTER TABLE `classroom` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `classroomfee`
--

DROP TABLE IF EXISTS `classroomfee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classroomfee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classRoomId` int(11) NOT NULL,
  `amount` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=990026 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classroomfee`
--

LOCK TABLES `classroomfee` WRITE;
/*!40000 ALTER TABLE `classroomfee` DISABLE KEYS */;
INSERT INTO `classroomfee` VALUES (990001,50001,'200000'),(990003,50002,'200000'),(990004,50003,'200000'),(990005,50004,'200000'),(990006,50005,'250000'),(990007,50006,'250000'),(990008,50007,'250000'),(990009,50008,'250000'),(990010,50009,'250000'),(990011,50010,'250000'),(990012,50011,'250000'),(990013,50012,'250000'),(990014,50013,'250000'),(990015,50014,'250000'),(990016,50015,'250000'),(990017,50016,'250000'),(990018,50017,'270000'),(990019,50018,'270000'),(990020,50019,'270000'),(990021,50020,'270000'),(990022,50021,'270000'),(990023,50022,'270000'),(990024,50023,'270000'),(990025,50024,'270000');
/*!40000 ALTER TABLE `classroomfee` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `document`
--

DROP TABLE IF EXISTS `document`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `description` varchar(20) NOT NULL,
  `format` varchar(20) NOT NULL,
  `belongTo` int(11) NOT NULL,
  `uploadDate` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `lastUpdateBy` int(11) NOT NULL,
  `locked` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `document`
--

LOCK TABLES `document` WRITE;
/*!40000 ALTER TABLE `document` DISABLE KEYS */;
/*!40000 ALTER TABLE `document` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(150) DEFAULT NULL,
  `lastname` varchar(150) NOT NULL,
  `street` varchar(150) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zipCode` varchar(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  `birthday` date NOT NULL,
  `email` varchar(100) NOT NULL,
  `cellPhone` varchar(30) NOT NULL,
  `workPhone` varchar(30) NOT NULL,
  `isPrincipalTeacherOfClassRoom` varchar(50) DEFAULT NULL,
  `personToCallByEmergencyCase` varchar(50) DEFAULT NULL,
  `numberToCallByEmergencyCase` varchar(30) DEFAULT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `matriculationNumber` varchar(100) NOT NULL,
  `profession` varchar(100) NOT NULL,
  `placeOfBirth` varchar(45) NOT NULL,
  `firstLogin` date DEFAULT NULL,
  `lastLogin` date DEFAULT NULL,
  `createdby` varchar(45) DEFAULT NULL,
  `isOnline` bit(1) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `userPrivileg` varchar(45) DEFAULT NULL,
  `iban` varchar(50) DEFAULT NULL,
  `bic` varchar(45) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `maritalStatus` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniEmployee` (`firstname`,`lastname`,`birthday`),
  UNIQUE KEY `isPrincipalTeacherOfClassRoom` (`isPrincipalTeacherOfClassRoom`)
) ENGINE=InnoDB AUTO_INCREMENT=30012 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `employee`
--

LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `employee` VALUES (30001,'Serge','Mbock','Rue de la gare22','Yaounde','1218','Cameroun','1945-01-01','sergeM@yahoo.fr','699834592','22248590',NULL,'Son epouse','69347729','\0','2015-01-01','2015-01-01','MbSerg12365Bo','Professeur de Francais 6em','Yaounde',NULL,NULL,NULL,'\0','21232f297a57a5a743894a0e4a801fc3','high',NULL,NULL,NULL,NULL),(30002,'Elliot','Wera','Avenue trois poteau 10','Yaounde','1223','Cameroun','1977-11-11','elliotWe@cam.de','698562781','23347637',NULL,'Son Epouse','69978237','\0','2015-11-11','2015-11-11','WerEll1327Wed','Professeur d Histoire ','Bonapriso',NULL,NULL,NULL,'\0',NULL,NULL,NULL,NULL,NULL,NULL),(30004,'Stefan Cyril','Ondoua','Avenue central 15','Mendon','1023','Cameroun','1983-12-12','stefOnd@caramail.fr','69956283','22397573',NULL,'Son Frere','69906537','\0','2015-01-02','2015-01-02','SeOn234erB','Gardinier','Konsamba',NULL,NULL,NULL,'\0',NULL,NULL,NULL,NULL,NULL,NULL),(30005,'Stefany','Mvoumela','Rue Montblan15','Madagascar','1133','Cameroun','1982-11-11','setfMv@caramail.fr','69997638','22365547',NULL,'Son Frere','69967337','\0','2015-01-02','2015-01-02','MvSte234erB','Cuisiniere','Manfe',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30006,'Helene','Bisombi','Avenu bubinga 12','Biyem-Assi','1425','Cameroun','1972-11-11','biss@caramail.fr','69763838','22657547',NULL,'Son Mari','69877377','\0','2015-01-02','2015-01-02','BisH234erB','Professeur de Religion','Ebombo',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30007,'Armand Laurent','Nkodo','Citee la joie 12','Mvog-Bi','1125','Cameroun','1977-11-11','nkd@caramail.fr','69802738','21238637',NULL,'Son Frere','69997377','\0','2015-01-02','2015-01-02','nkH234erB','Professeur de Maths','Yaounde',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30008,'Serge Andre','Fotso','Rue du stade 12','Omnisport','1525','Cameroun','1967-11-11','ludC@caramail.fr','69682996','21527737',NULL,'Son Frere','69889970','\0','2015-01-02','2015-01-02','LudCh234erB','Professeur de Maths','Bana',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30009,'Simon Franck','Mbangui','La montee rouge 15','Montee Jovence','1525','Cameroun','1969-10-10','mbEdC@caramail.fr','69987606','21228768',NULL,'Son Epouse','69987700','\0','2015-01-02','2015-01-02','mhRD234erC','Professeur de Francais','Limbe',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30010,'Marie Silviane','Mbangui','La montee rouge 15','Montee Jovence','1525','Cameroun','1980-12-12','mbSylC@caramail.fr','69977806','21228768',NULL,'Son Epous','69989700','\0','2015-01-02','2015-01-02','LudCh234erB','Professeur de Francais','Douala',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(30011,'Francois','Oumarou','18','Carrefourd 2 Poteau','1825','Cameroun','1970-12-12','ouOu@caramail.fr','69908796','21225678',NULL,'Son Epouse','69911780','\0','2015-01-02','2015-01-02','ouO234erBr','Professeur de Maths','Maroua',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employeesalarycategory`
--

DROP TABLE IF EXISTS `employeesalarycategory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employeesalarycategory` (
  `id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `salaryClass` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `employeesalarycategory`
--

LOCK TABLES `employeesalarycategory` WRITE;
/*!40000 ALTER TABLE `employeesalarycategory` DISABLE KEYS */;
/*!40000 ALTER TABLE `employeesalarycategory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `start` varchar(20) NOT NULL,
  `finish` varchar(20) NOT NULL,
  `day` varchar(20) NOT NULL,
  `organisatorId` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event`
--

LOCK TABLES `event` WRITE;
/*!40000 ALTER TABLE `event` DISABLE KEYS */;
/*!40000 ALTER TABLE `event` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exammark`
--

DROP TABLE IF EXISTS `exammark`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exammark` (
  `id` int(11) NOT NULL,
  `mark` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exammark`
--

LOCK TABLES `exammark` WRITE;
/*!40000 ALTER TABLE `exammark` DISABLE KEYS */;
/*!40000 ALTER TABLE `exammark` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `generalmark`
--

DROP TABLE IF EXISTS `generalmark`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `generalmark` (
  `id` int(11) NOT NULL,
  `quarter` varchar(45) NOT NULL,
  `year` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `generalmark`
--

LOCK TABLES `generalmark` WRITE;
/*!40000 ALTER TABLE `generalmark` DISABLE KEYS */;
/*!40000 ALTER TABLE `generalmark` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `image`
--

DROP TABLE IF EXISTS `image`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `objectId` int(11) NOT NULL,
  `location` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110007 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `image`
--

LOCK TABLES `image` WRITE;
/*!40000 ALTER TABLE `image` DISABLE KEYS */;
INSERT INTO `image` VALUES (110006,100001,'C:\\Users\\Claude\\Downloads\\young-black-businessman.png');
/*!40000 ALTER TABLE `image` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `infrastructur`
--

DROP TABLE IF EXISTS `infrastructur`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `infrastructur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infrastructurCategoryId` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `numberOfSits` int(11) NOT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `personInCharge` varchar(50) DEFAULT NULL,
  `state` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `infrastructurCategoryId` (`infrastructurCategoryId`),
  CONSTRAINT `infrastructur_ibfk_1` FOREIGN KEY (`infrastructurCategoryId`) REFERENCES `infrastructurcategory` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60004 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `infrastructur`
--

LOCK TABLES `infrastructur` WRITE;
/*!40000 ALTER TABLE `infrastructur` DISABLE KEYS */;
INSERT INTO `infrastructur` VALUES (60001,70015,'Buro Principal','Buro du principal',3,'\0','2015-01-01','2015-01-01','Mr. Ondoua','new'),(60002,70015,'Buro Vice Principal','Buro du vice principal',5,'\0','2015-01-01','2015-01-01','Mr. Ondoua','new'),(60003,70015,'Buro surveillant general','Buro du surveillant genera',5,'\0','2015-01-01','2015-01-01','Mr. Owoundi','new');
/*!40000 ALTER TABLE `infrastructur` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `infrastructurcategory`
--

DROP TABLE IF EXISTS `infrastructurcategory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `infrastructurcategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70018 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `infrastructurcategory`
--

LOCK TABLES `infrastructurcategory` WRITE;
/*!40000 ALTER TABLE `infrastructurcategory` DISABLE KEYS */;
INSERT INTO `infrastructurcategory` VALUES (70001,'6 eme','Class'),(70002,'5 eme','Class'),(70003,'4 eme','Class'),(70004,'3 eme','Class'),(70005,'2nde','Class'),(70006,'1ere','Class'),(70007,'Tle','Class'),(70009,'infrastructure de sante','sante'),(70010,'infrastructure de sport','sport'),(70011,'autres','autres'),(70012,'infrastructure sanitaire','sanitaire'),(70015,'infrastructure de base','buro'),(70016,'infrastructure de cullinaire','culinaire'),(70017,'infrastructure du bien étre','bien étre');
/*!40000 ALTER TABLE `infrastructurcategory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `infrastructurmaterial`
--

DROP TABLE IF EXISTS `infrastructurmaterial`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `infrastructurmaterial` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infrastructurId` int(11) DEFAULT NULL,
  `materialId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `infrastructurId` (`infrastructurId`),
  KEY `materialId` (`materialId`),
  CONSTRAINT `infrastructurmaterial_ibfk_1` FOREIGN KEY (`infrastructurId`) REFERENCES `infrastructur` (`id`),
  CONSTRAINT `infrastructurmaterial_ibfk_2` FOREIGN KEY (`materialId`) REFERENCES `material` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `infrastructurmaterial`
--

LOCK TABLES `infrastructurmaterial` WRITE;
/*!40000 ALTER TABLE `infrastructurmaterial` DISABLE KEYS */;
/*!40000 ALTER TABLE `infrastructurmaterial` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `mark`
--

DROP TABLE IF EXISTS `mark`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mark` (
  `id` int(11) NOT NULL,
  `subjectId` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  `examMarkId` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `quarter` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mark`
--

LOCK TABLES `mark` WRITE;
/*!40000 ALTER TABLE `mark` DISABLE KEYS */;
/*!40000 ALTER TABLE `mark` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `material`
--

DROP TABLE IF EXISTS `material`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `material` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `inUse` bit(1) NOT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` int(11) NOT NULL,
  `lastUpdate` varchar(15) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `state` varchar(255) NOT NULL,
  `serialnumber` varchar(45) DEFAULT NULL,
  `personInCharge` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `material`
--

LOCK TABLES `material` WRITE;
/*!40000 ALTER TABLE `material` DISABLE KEYS */;
/*!40000 ALTER TABLE `material` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `materialcategory`
--

DROP TABLE IF EXISTS `materialcategory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `materialcategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `materialcategory`
--

LOCK TABLES `materialcategory` WRITE;
/*!40000 ALTER TABLE `materialcategory` DISABLE KEYS */;
/*!40000 ALTER TABLE `materialcategory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `notification`
--

DROP TABLE IF EXISTS `notification`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `notification` (
  `id` int(11) NOT NULL,
  `description` varchar(100) NOT NULL,
  `From` varchar(45) NOT NULL,
  `to` varchar(45) NOT NULL,
  `status` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `notification`
--

LOCK TABLES `notification` WRITE;
/*!40000 ALTER TABLE `notification` DISABLE KEYS */;
/*!40000 ALTER TABLE `notification` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payment`
--

DROP TABLE IF EXISTS `payment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payment` (
  `id` int(11) NOT NULL,
  `from` varchar(45) DEFAULT NULL,
  `to` varchar(45) DEFAULT NULL,
  `executionDate` varchar(45) DEFAULT NULL,
  `quarter` varchar(45) DEFAULT NULL,
  `executeBy` varchar(45) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payment`
--

LOCK TABLES `payment` WRITE;
/*!40000 ALTER TABLE `payment` DISABLE KEYS */;
/*!40000 ALTER TABLE `payment` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classroomId` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `street` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zipCode` varchar(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  `birthday` date NOT NULL,
  `email` varchar(100) NOT NULL,
  `cellPhone` varchar(30) NOT NULL,
  `personToCallByEmergencyCase` varchar(50) DEFAULT NULL,
  `numberToCallByEmergencyCase` varchar(30) DEFAULT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `matriculationNumber` varchar(100) NOT NULL,
  `placeOfBirth` varchar(100) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `fatherName` varchar(100) DEFAULT NULL,
  `motherName` varchar(100) DEFAULT NULL,
  `lastUpdate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniStudent` (`firstname`,`lastname`,`birthday`),
  KEY `classroomId` (`classroomId`),
  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`classroomId`) REFERENCES `classroom` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20005 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (20001,50001,'Franck','Simboue','Rue de la citée','Yaounde','1352','Cameroon','2001-11-11','msFr@yahoo.fr','','Sa maman','69976452','\0','2015-01-01','siFr1290em','Douala Cameroon','masculin','Mr. Simboue Andre','Mme Edoua Arlette',NULL),(20002,50002,'Merlin','Yomba','Quartier latin','Douala','2892','Cameroun','2000-10-10','merYo@hotmail.com','69975428','Son Frere','67756274','\0','2015-01-01','meYo2119om','Douala Cameroon','masculin','Mr.Yomba','Mme Ngo Doue Helene',NULL),(20003,50002,'Ferdinan','Ondoua','Rue de Babino 2','Yaounde','1398','Cameroun','2000-01-10','ferOn@hotmail.com','69975428','Son Frere','67756274','\0','2015-01-01','feOn4819om','Douala Cameroon','masculin','Mr.Ondoua maurice','Mme Etoundi Carine',NULL),(20004,50002,'Francy','Ekosso','Rue de Garage citee verte ','Yaounde','1496','Cameroun','2000-03-10','frOkk@hotmail.com','69983021','Son Frere','69956294','\0','2015-01-01','frEkk3956em','Edéa Cameroon','feminin','Mr.Ekosso rené','Mme Moungoué Norelle',NULL);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `subject`
--

DROP TABLE IF EXISTS `subject`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `coefficient` int(11) NOT NULL,
  `className` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`className`)
) ENGINE=InnoDB AUTO_INCREMENT=90142 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `subject`
--

LOCK TABLES `subject` WRITE;
/*!40000 ALTER TABLE `subject` DISABLE KEYS */;
INSERT INTO `subject` VALUES (90001,'Maths',4,'6 eme A'),(90002,'Maths',4,'6 eme B'),(90003,'Maths',4,'6 eme C'),(90004,'Maths',4,'6 eme D'),(90005,'Maths',4,'6 eme E'),(90006,'Maths',4,'5 eme A'),(90007,'Maths',4,'5 eme B'),(90008,'Maths',4,'5 eme C'),(90009,'Maths',4,'5 eme D'),(90010,'Maths',4,'5 eme E'),(90011,'Maths',4,'4 eme A'),(90012,'Maths',4,'4 eme B'),(90013,'Maths',4,'4 eme C'),(90014,'Maths',4,'4 eme D'),(90015,'Maths',4,'4 eme E'),(90016,'Maths',5,'3 eme A'),(90017,'Maths',5,'3 eme B'),(90018,'Maths',5,'3 eme C'),(90019,'Maths',5,'3 eme D'),(90020,'Maths',3,'2 eme A'),(90021,'Maths',4,'2 eme SES'),(90022,'Maths',5,'2 eme C1'),(90023,'Maths',5,'2 eme C2'),(90024,'Maths',5,'2 eme C3'),(90025,'Maths',3,'P ere A'),(90026,'Maths',4,'P ere SES'),(90027,'Maths',5,'P ere C1'),(90028,'Maths',4,'P ere D1'),(90029,'Maths',4,'P ere D2'),(90030,'Maths',3,'Tle A'),(90031,'Maths',4,'Tle SES'),(90032,'Maths',5,'Tle C1'),(90033,'Maths',4,'Tle D1'),(90034,'Maths',4,'Tle D2'),(90035,'Maths',5,'2 eme C4'),(90036,'Francais',4,'4 eme A'),(90037,'Francais',4,'4 eme B'),(90038,'Francais',4,'4 eme C'),(90039,'Francais',4,'4 eme D'),(90040,'Francais',4,'4 eme E'),(90041,'Francais',5,'3 eme A'),(90042,'Francais',5,'3 eme B'),(90043,'Francais',5,'3 eme C'),(90044,'Francais',5,'3 eme D'),(90045,'Francais',3,'2 eme A'),(90046,'Francais',4,'2 eme SES'),(90047,'Francais',5,'2 eme C1'),(90048,'Francais',5,'2 eme C2'),(90049,'Francais',5,'2 eme C3'),(90050,'Francais',3,'P ere A'),(90051,'Francais',4,'P ere SES'),(90052,'Francais',5,'P ere C1'),(90053,'Francais',4,'P ere D1'),(90054,'Francais',4,'P ere D2'),(90055,'Francais',3,'Tle A'),(90056,'Francais',4,'Tle SES'),(90057,'Francais',5,'Tle C1'),(90058,'Francais',4,'Tle D1'),(90059,'Francais',4,'Tle D2'),(90060,'Francais',5,'2 eme C4'),(90061,'Francais',4,'6 eme A'),(90062,'Francais',4,'6 eme B'),(90063,'Francais',4,'6 eme C'),(90064,'Francais',4,'6 eme D'),(90065,'Francais',4,'6 eme E'),(90066,'Francais',4,'5 eme A'),(90067,'Francais',4,'5 eme B'),(90068,'Francais',4,'5 eme C'),(90069,'Francais',4,'5 eme D'),(90070,'Francais',4,'5 eme E'),(90074,'Histoire',4,'6 eme A'),(90075,'Histoire',4,'6 eme B'),(90076,'Histoire',4,'6 eme C'),(90077,'Histoire',4,'6 eme D'),(90078,'Histoire',4,'6 eme E'),(90079,'Histoire',4,'5 eme A'),(90080,'Histoire',4,'5 eme B'),(90081,'Histoire',4,'5 eme C'),(90082,'Histoire',4,'5 eme D'),(90083,'Histoire',4,'5 eme E'),(90084,'Histoire',4,'4 eme A'),(90085,'Histoire',4,'4 eme B'),(90086,'Histoire',4,'4 eme C'),(90087,'Histoire',4,'4 eme D'),(90088,'Histoire',4,'4 eme E'),(90089,'Histoire',5,'3 eme A'),(90090,'Histoire',5,'3 eme B'),(90091,'Histoire',5,'3 eme C'),(90092,'Histoire',5,'3 eme D'),(90093,'Histoire',3,'2 eme A'),(90094,'Histoire',4,'2 eme SES'),(90095,'Histoire',5,'2 eme C1'),(90096,'Histoire',5,'2 eme C2'),(90097,'Histoire',5,'2 eme C3'),(90098,'Histoire',3,'P ere A'),(90099,'Histoire',4,'P ere SES'),(90100,'Histoire',5,'P ere C1'),(90101,'Histoire',4,'P ere D1'),(90102,'Histoire',4,'P ere D2'),(90103,'Histoire',3,'Tle A'),(90104,'Histoire',4,'Tle SES'),(90105,'Histoire',5,'Tle C1'),(90106,'Histoire',4,'Tle D1'),(90107,'Histoire',4,'Tle D2'),(90108,'Religion',4,'6 eme A'),(90109,'Religion',4,'6 eme B'),(90110,'Religion',4,'6 eme C'),(90111,'Religion',4,'6 eme D'),(90112,'Religion',4,'6 eme E'),(90113,'Religion',4,'5 eme A'),(90114,'Religion',4,'5 eme B'),(90115,'Religion',4,'5 eme C'),(90116,'Religion',4,'5 eme D'),(90117,'Religion',4,'5 eme E'),(90118,'Religion',4,'4 eme A'),(90119,'Religion',4,'4 eme B'),(90120,'Religion',4,'4 eme C'),(90121,'Religion',4,'4 eme D'),(90122,'Religion',4,'4 eme E'),(90123,'Religion',5,'3 eme A'),(90124,'Religion',5,'3 eme B'),(90125,'Religion',5,'3 eme C'),(90126,'Religion',5,'3 eme D'),(90127,'Religion',3,'2 eme A'),(90128,'Religion',4,'2 eme SES'),(90129,'Religion',5,'2 eme C1'),(90130,'Religion',5,'2 eme C2'),(90131,'Religion',5,'2 eme C3'),(90132,'Religion',3,'P ere A'),(90133,'Religion',4,'P ere SES'),(90134,'Religion',5,'P ere C1'),(90135,'Religion',4,'P ere D1'),(90136,'Religion',4,'P ere D2'),(90137,'Religion',3,'Tle A'),(90138,'Religion',4,'Tle SES'),(90139,'Religion',5,'Tle C1'),(90140,'Religion',4,'Tle D1'),(90141,'Religion',4,'Tle D2');
/*!40000 ALTER TABLE `subject` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `subjectprofessor`
--

DROP TABLE IF EXISTS `subjectprofessor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subjectprofessor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectId` int(11) NOT NULL,
  `professorId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=120002 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `subjectprofessor`
--

LOCK TABLES `subjectprofessor` WRITE;
/*!40000 ALTER TABLE `subjectprofessor` DISABLE KEYS */;
INSERT INTO `subjectprofessor` VALUES (120001,90061,30001);
/*!40000 ALTER TABLE `subjectprofessor` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `isDeleted` bit(1) DEFAULT NULL,
  `userPrivileg` varchar(15) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `cellPhone` varchar(30) DEFAULT NULL,
  `workPhone` varchar(30) DEFAULT NULL,
  `createdby` varchar(100) DEFAULT NULL,
  `firstLogin` date DEFAULT NULL,
  `lastLogin` date DEFAULT NULL,
  `isOnline` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniUser` (`username`,`password`),
  UNIQUE KEY `username_2` (`username`,`id`),
  UNIQUE KEY `username_3` (`username`,`password`),
  KEY `id` (`id`),
  KEY `id_2` (`id`),
  KEY `username` (`username`),
  KEY `password` (`password`)
) ENGINE=InnoDB AUTO_INCREMENT=100003 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (100001,'Admin','21232f297a57a5a743894a0e4a801fc3','\0','high','Administrateur','Lastname','Alte-Reutstr.60','Nuremberg','Germany','admin@admin.de','4917683994375','4991125382643','Admin','2012-05-20','2012-06-20',''),(100002,'Onana','21232f297a57a5a743894a0e4a801fc3','\0','low','Onana','Francois','Rue de palmier.6','Yaounde','Cameroon','onnana@ecole.cm','23799467729','23923477893','Admin','2015-05-15','2015-03-15','\0');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `weeklyplan`
--

DROP TABLE IF EXISTS `weeklyplan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `weeklyplan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectId` int(11) DEFAULT NULL,
  `start` varchar(20) NOT NULL,
  `finish` varchar(20) NOT NULL,
  `day` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subjectId` (`subjectId`),
  CONSTRAINT `weeklyplan_ibfk_1` FOREIGN KEY (`subjectId`) REFERENCES `subject` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `weeklyplan`
--

LOCK TABLES `weeklyplan` WRITE;
/*!40000 ALTER TABLE `weeklyplan` DISABLE KEYS */;
/*!40000 ALTER TABLE `weeklyplan` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'camschooldb'
--

--
-- Dumping routines for database 'camschooldb'
--
/*!50003 DROP PROCEDURE IF EXISTS `createEmployeeProcedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `createEmployeeProcedure`(
OUT employeeId int(11),
IN firstname varchar(150),
IN lastname varchar(150), 
IN street varchar(150), 
IN city varchar(100), 
IN zipCode varchar(10), 
IN country varchar(255), 
IN birthday date,
IN email varchar(100), 
IN cellPhone varchar(30), 
IN workPhone varchar(30), 
IN isPrincipalTeacherOfClassRoom varchar(50), 
IN personToCallByEmergencyCase varchar(50),
IN numberToCallByEmergencyCase varchar(30),
IN isDeleted bit(1), 
IN firstRegistration date,
IN lastUpdate date,
IN matriculationNumber varchar(100), 
IN profession varchar(100), 
IN placeOfBirth varchar(45), 
IN firstLogin date,
IN lastLogin date,
IN createdby varchar(45), 
IN isOnline bit(1),
IN password varchar(45), 
IN userPrivileg varchar(45), 
IN iban varchar(50), 
IN bic varchar(45), 
IN gender varchar(45), 
IN maritalStatus varchar(45),
IN pictureLocation varchar(255)
)
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;

INSERT INTO employee
(`firstname`,
`lastname`,
`street`,
`city`,
`zipCode`,
`country`,
`birthday`,
`email`,
`cellPhone`,
`workPhone`,
`isPrincipalTeacherOfClassRoom`,
`personToCallByEmergencyCase`,
`numberToCallByEmergencyCase`,
`isDeleted`,
`firstRegistration`,
`lastUpdate`,
`matriculationNumber`,
`profession`,
`placeOfBirth`,
`firstLogin`,
`lastLogin`,
`createdby`,
`isOnline`,
`password`,
`userPrivileg`,
`iban`,
`bic`,
`gender`, 
`maritalStatus`)
VALUES
(firstname,
lastname, 
street, 
city, 
zipCode,  
country, 
birthday, 
email, 
cellPhone, 
workPhone, 
isPrincipalTeacherOfClassRoom, 
personToCallByEmergencyCase, 
numberToCallByEmergencyCase, 
isDeleted,  
firstRegistration, 
lastUpdate, 
matriculationNumber, 
profession,  
placeOfBirth, 
firstLogin, 
lastLogin, 
createdby, 
isOnline, 
password, 
userPrivileg, 
iban, 
bic, 
gender, 
maritalStatus
);


SELECT LAST_INSERT_ID() into employeeId;


COMMIT;
     
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `getAllPictureProcedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllPictureProcedure`(IN paramstr VARCHAR(20), out total int(11))
BEGIN
    SELECT COUNT(*) INTO total FROM `camschooldb`.`picture`;
    if(total > 0) then
		set total = 144;    
    else
		set total = 198;   
    
    end if;   
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `userLoginProcedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `userLoginProcedure`(
IN usernameIn varchar(150),
IN passwordIN varchar(150),
OUT id int(11),
OUT username varchar(150), 
OUT password varchar(150), 
OUT userPrivileg varchar(15), 
OUT firstname varchar(10), 
OUT lastname varchar(255), 
OUT street varchar(150),
out city varchar(100), 
OUT country varchar(50), 
OUT email varchar(255), 
OUT cellPhone varchar(50), 
OUT workPhone varchar(50)
)
BEGIN

SELECT `user`.`id`,
    `user`.`username`,
    `user`.`password`,
    `user`.`isDeleted`,
    `user`.`userPrivileg`,
    `user`.`firstname`,
    `user`.`lastname`,
    `user`.`street`,
    `user`.`city`,
    `user`.`country`,
    `user`.`email`,
    `user`.`cellPhone`,
    `user`.`workPhone`
FROM `camschooldb`.`user`
where username = usernameIn and password = passwordIn;

UPDATE `camschooldb`.`user`
SET
`isOnline` = 1
WHERE username = usernameIn and password = passwordIn;

     
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-02-09 18:29:04
